package com.example.booksystem.dao;

import com.example.booksystem.bean.Record;
import com.example.booksystem.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class RecordDao {
    public List<Record> getRecordByBookId(long bookId) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<Record> list = new ArrayList<>();

        try {
            connection = DBUtil.getConnection();
            String sql ="select * from record where bookId= ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1,bookId);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                list.add(new Record(resultSet.getLong(1),resultSet.getLong(2),resultSet.getLong(3),resultSet.getDate(4),resultSet.getDate(5),resultSet.getDouble(6),resultSet.getLong(7),resultSet.getString(8)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return list;
    }
    /**
     * 根据用户的身份证号查询用户借阅信息
     * @param idNum
     * @return
     */
    public List<Record> getRecordsByIdNum(String idNum) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<Record> list = new ArrayList<>();

        try {
            connection = DBUtil.getConnection();
            String sql ="select * from record where memberId = (select id from member where idNumber = ?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,idNum);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                //list.add(new Member(resultSet.getLong(1),resultSet.getString(2),resultSet.getString(3),resultSet.getLong(4),resultSet.getDouble(5),resultSet.getDate(6),resultSet.getString(7),resultSet.getString(8)));
                list.add(new Record(resultSet.getLong(1),resultSet.getLong(2),resultSet.getLong(3),resultSet.getDate(4),resultSet.getDate(5),resultSet.getDouble(6),resultSet.getLong(7),resultSet.getString(8)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return list;
    }

    /**
     * 根据用户的会员编号查询用户借阅信息
     * @param memberId
     * @return
     */
    public List<Record> getRecordsByMemberId(long memberId) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<Record> list = new ArrayList<>();

        try {
            connection = DBUtil.getConnection();
            String sql ="select * from record where memberId= ? and backDate is null";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1,memberId);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                list.add(new Record(resultSet.getLong(1),resultSet.getLong(2),resultSet.getLong(3),resultSet.getDate(4),resultSet.getDate(5),resultSet.getDouble(6),resultSet.getLong(7),resultSet.getString(8)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return list;
    }

    /**
     * 添加借阅记录
     * @param memberId
     * @param bookId
     * @param deposit
     * @param userId
     * @return
     * @throws SQLException
     */
    public int add(long memberId,long bookId,double deposit,long userId) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int result = 0;
        try {
            connection = DBUtil.getConnection();
            String sql="insert into record values(null,?,?,CURRENT_DATE,null,?,?,'978-7-302-12260-9')";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1,memberId);
            preparedStatement.setLong(2,bookId);
            preparedStatement.setDouble(3,deposit);
            preparedStatement.setLong(4,userId);
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return result;

    }

    /**
     *
     * @param deposit 押金: 过期归还，>0  准时归还:清零
     * @param userId 管理员编号
     * @param id 记录编号
     * @return
     */
    public  int modify(double deposit,long userId,long id){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int result = 0;
        try {
            connection = DBUtil.getConnection();
            String sql = "update  record set backDate = CURRENT_DATE,deposit = ?,userId = ? where id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setDouble(1,deposit);
            preparedStatement.setLong(2,userId);
            preparedStatement.setLong(3,id);
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return result;

    }

    public  int modify(long id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int result = 0;
        try {
            connection = DBUtil.getConnection();
            String sql = "update  record set rentDate = CURRENT_DATE where id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setDouble(1,id);
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return result;

    }



    public Record getById(long recordId) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Record record = new Record();

        try {
            connection = DBUtil.getConnection();
            String sql ="select * from record where id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1,recordId);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                //list.add(new Member(resultSet.getLong(1),resultSet.getString(2),resultSet.getString(3),resultSet.getLong(4),resultSet.getDouble(5),resultSet.getDate(6),resultSet.getString(7),resultSet.getString(8)));
                record = new Record(resultSet.getLong(1),resultSet.getLong(2),resultSet.getLong(3),resultSet.getDate(4),resultSet.getDate(5),resultSet.getDouble(6),resultSet.getLong(7),resultSet.getString(8));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return record;
    }

    /**
     * 全部:0
     *     已归还:1
     *    未归还 :2
     *    最近一周需归还:3
     * @param typeId
     * @param keyWork
     * @return
     * @throws SQLException
     */

    public  List<Map<String,Object>>  query(int typeId,String keyWork) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<Map<String, Object>> list = new ArrayList<>();

        StringBuilder sb = new StringBuilder("select * from recordView where 1=1 ");
        switch (typeId){
            case 0:
                break;
            case 1:
                sb.append("and  backDate is not null ");
                break;
            case 2:
                sb.append("and  backDate is  null ");
                break;
            case 3:
                sb.append("and  backDate is null and  returnDate < date_add(CURRENT_DATE,interval 7 DAY) ");
                break;
        }
        if(keyWork!=null){
            sb.append(" and bookName like '%"+keyWork+"%' or memberName like '%"+keyWork+"%' or   concat(rentDate,'') like '%"+keyWork+"%'");
        }

        try {
            connection = DBUtil.getConnection();
            String sql = sb.toString();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Map<String, Object> map = new LinkedHashMap<>();
                map.put("id",resultSet.getLong(1));
                map.put("memberName",resultSet.getString(2));
                map.put("bookName",resultSet.getString(3));
                map.put("rentDate",resultSet.getDate(4));
                map.put("backDate",resultSet.getDate(5));
                map.put("returnDate",resultSet.getDate(6));
                map.put("deposit",resultSet.getFloat(7));
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return list;
    }

    public static void main(String[] args) {
        RecordDao recordDao = new RecordDao();
        try {
            List<Map<String, Object>> list = recordDao.query(0, "百");
            for (Map<String, Object> stringObjectMap : list) {
                System.out.println(stringObjectMap);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}
